<!---
Copyright: (c) 2006 The MachBlog Authors
Authors: Matt Woodward (mpwoodward@gmail.com) & Peter J. Farrell (pjf@maestropublishing.com)

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License. 
You may obtain a copy of the License at 

http://www.apache.org/licenses/LICENSE-2.0 

Unless required by applicable law or agreed to in writing, software 
distributed under the License is distributed on an "AS IS" BASIS, 
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
See the License for the specific language governing permissions and 
limitations under the License.

$Id: TrackbackGateway.cfc 948 2006-08-24 01:24:39Z mpwoodward $

Notes:
--->

<cfcomponent 
	displayname="TrackbackGateway" 
	output="false" 
	extends="TrackbackGateway" 
	hint="TrackbackGateway for MachBlog (MSSQL)">
	
	<cffunction name="init" access="public" output="false" returntype="TrackbackGateway" hint="Constructor for this CFC">
		<cfreturn this />
	</cffunction>

	<cffunction name="getTrackbacks" access="public" output="false" returntype="query" 
			hint="Returns a query containing all the trackbacks for a particular entry">
		<cfargument name="entryId" type="string" required="true" />
		<cfargument name="getAll" type="boolean" required="false" default="false" />
		
		<cfset var getTrackbacks = "" />
		<cfset var getBlockedIps = "" />
		
		<cftry>
			<cfquery name="getTrackbacks" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#">
				SELECT * 
				FROM machblog_trackback 
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
												maxlength="35" /> 
				ORDER BY dt_created ASC
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error [databaseerror]" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfset QueryAddColumn(getTrackbacks, "ip_is_blocked", ArrayNew(1)) />
		
		<cfif IsQuery(getTrackbacks) AND getTrackbacks.RecordCount GT 0>
			<cftry>
				<cfquery name="getBlockedIps" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
					SELECT blocked_ip 
					FROM machblog_blocked_ip 
					WHERE blocked_ip IN (#QuotedValueList(getTrackbacks.ip_created)#) 
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error [databaseerror]" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
		
		<cfloop query="getTrackbacks">
			<cfif IsQuery(getBlockedIps) AND getBlockedIps.RecordCount GT 0>
				<cfif ListFind(ValueList(getBlockedIps.blocked_ip), getTrackbacks.ip_created)>
					<cfset QuerySetCell(getTrackbacks, "ip_is_blocked", 1, getTrackbacks.CurrentRow) />
				</cfif>
				
				<cfelse>
					<cfset QuerySetCell(getTrackbacks, "ip_is_blocked", 0, getTrackbacks.CurrentRow) />
			</cfif>
		</cfloop>
			
		<cfreturn getTrackbacks />
	</cffunction>
	
	<cffunction name="getRecentTrackbacks" access="public" output="false" returntype="query" 
			hint="Returns a query containing recent trackbacks across all entries">
		<cfargument name="numToGet" type="numeric" required="true" />
		<cfargument name="includeInactive" type="boolean" required="false" default="false" />
		
		<cfset var getTrackbacks = "" />
		<cfset var getBlockedIps = "" />
		
		<cftry>
			<cfquery name="getTrackbacks" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#" maxrows="#arguments.numToGet#">
				SELECT 	machblog_trackback.*, 
						machblog_entry.entry_id, 
						machblog_entry.title 
				FROM 	machblog_trackback 
				LEFT OUTER JOIN machblog_entry ON machblog_trackback.entry_id = machblog_entry.entry_id 
				<cfif Not arguments.includeInactive>
					WHERE machblog_trackback.is_active = 
												<cfqueryparam value="1" cfsqltype="cf_sql_bit" /> 
				</cfif> 
				ORDER BY machblog_trackback.dt_created ASC
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfset QueryAddColumn(getTrackbacks, "ip_is_blocked", ArrayNew(1)) />
		
		<cfif IsQuery(getTrackbacks) AND getTrackbacks.RecordCount GT 0>
			<cftry>
				<cfquery name="getBlockedIps" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
					SELECT blocked_ip 
					FROM machblog_blocked_ip 
					WHERE blocked_ip IN (#QuotedValueList(getTrackbacks.ip_created)#) 
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error [databaseerror]" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
		
		<cfloop query="getTrackbacks">
			<cfif IsQuery(getBlockedIps) AND getBlockedIps.RecordCount GT 0>
				<cfif ListFind(ValueList(getBlockedIps.blocked_ip), getTrackbacks.ip_created)>
					<cfset QuerySetCell(getTrackbacks, "ip_is_blocked", 1, getTrackbacks.CurrentRow) />
				</cfif>
				
				<cfelse>
					<cfset QuerySetCell(getTrackbacks, "ip_is_blocked", 0, getTrackbacks.CurrentRow) />
			</cfif>
		</cfloop>
		
		<cfreturn getTrackbacks />
	</cffunction>
	
	<cffunction name="getTrackbackCountByDate" access="public" output="false" returntype="numeric" 
			hint="Returns the number of trackbacks added on a particular date">
		<cfargument name="trackbackDate" type="date" required="true" />
		
		<cfset var getTrackbackCount = "" />
		<cfset var trackbackCount = 0 />
		<cfset var startDate = CreateDateTime(DatePart("yyyy", arguments.trackbackDate), 
												DatePart("m", arguments.trackbackDate), 
												DatePart("d", arguments.trackbackDate), 
												00, 00, 00) />
		<cfset var endDate = CreateDateTime(DatePart("yyyy", arguments.trackbackDate), 
												DatePart("m", arguments.trackbackDate), 
												DatePart("d", arguments.trackbackDate), 
												23, 59, 59) />
		
		<!--- create epoch from start and end dates --->
		<cfset startDate = getResourceBundleFacade().getLocaleUtils().toEpoch(startDate) />
		<cfset endDate = getResourceBundleFacade().getLocaleUtils().toEpoch(endDate) />
		
		<cftry>
			<cfquery name="getTrackbackCount" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#">
				SELECT COUNT(machblog_trackback.entry_id) as trackback_count 
				FROM machblog_trackback 
				WHERE dt_created >= <cfqueryparam value="#startDate#" cfsqltype="cf_sql_bigint" /> 
				AND dt_created <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_bigint" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
		
		<cfif IsQuery(getTrackbackCount) AND getTrackbackCount.RecordCount GT 0>
			<cfset trackbackCount = getTrackbackCount.trackback_count />
		</cfif>
		
		<cfreturn trackbackCount />
	</cffunction>

	<cffunction name="deactivateTrackbacksByEntryId" access="public" output="false" returntype="void" 
			hint="Deactivates all trackbacks for a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var updateTrackbacks = "" />
		
		<cftry>
			<cfquery name="updateTrackbacks" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource.getUserName()#" 
						password="#getDatasource().getPassword()#">
				UPDATE machblog_trackback 
				SET is_active = <cfqueryparam value="0" cfsqltype="cf_sql_bit" /> 
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
												maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
	
	<cffunction name="activateTrackbacksByEntryId" access="public" output="false" returntype="void" 
			hint="Activates all trackbacks for a particular entry)">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var updateTrackbacks = "" />
		
		<cftry>
			<cfquery name="updateTrackbacks" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource.getUserName()#" 
						password="#getDatasource().getPassword()#">
				UPDATE machblog_trackback 
				SET is_active = <cfqueryparam value="1" cfsqltype="cf_sql_bit" /> 
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
												maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
	
	<cffunction name="deleteTrackbacksByEntryId" access="public" output="false" returntype="void" 
			hint="Deletes all trackbacks associated with a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />
		
		<cfset var deleteTrackbacks = "" />
		
		<cftry>
			<cfquery name="deleteTrackbacks" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#">
				DELETE FROM machblog_trackback 
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
												maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
	
	<cffunction name="toggleTrackbackDisplay" access="public" output="false" returntype="void" 
			hint="Approves or unapproves a trackback">
		<cfargument name="trackbackId" type="uuid" required="true" />
		<cfargument name="action" type="string" required="true" />
		<cfargument name="adminId" type="uuid" required="true" />

		<cfset var toggleTrackback = "" />
		<cfset var isActive = ""/>
		
		<cfif arguments.action EQ "show">
			<cfset isActive = 1 />
		<cfelseif arguments.action EQ "hide">
			<cfset isActive = 0 />
		</cfif>
		
		<cfif isActive NEQ "">
			<cftry>
				<cfquery name="toggleTrackback" datasource="#getDatasource().getDsn()#" 
						username="#getDatasource().getUserName()#" 
						password="#getDatasource().getPassword()#">
					UPDATE 	machblog_trackback 
					SET 	is_active = <cfqueryparam value="#isActive#" cfsqltype="bit" />, 
						 	modified_by_id = <cfqueryparam value="#arguments.adminId#" 
						 									cfsqltype="cf_sql_char" maxlength="35" />, 
						 	dt_modified = 
						 				<cfqueryparam value="#getResourceBundleFacade().getLocaleUtils().toEpoch(Now())#" 
						 								cfsqltype="cf_sql_bigint" />, 
						 	ip_modified = 
						 			<cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar" 
						 							maxlength="15" />
					WHERE 	trackback_id = <cfqueryparam value="#arguments.trackbackId#" 
															cfsqltype="cf_sql_char" maxlength="35" />
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
	</cffunction>
	
	<cffunction name="deleteTrackbacks" access="public" output="false" returntype="void" hint="Deletes trackbacks">
		<cfargument name="deleteIds" type="string" required="true" />

		<cfset var deleteTrackbacks = "" />
		
		<cftry>
			<cfquery name="deleteTrackbacks" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#">
				DELETE FROM machblog_trackback 
				WHERE trackback_id IN (<cfqueryparam value="#arguments.deleteIds#" list="true" />)
			</cfquery>
			<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
</cfcomponent>